interview

Home / DeveloperSection / Interviews / Why are use triggers in the SQL Server Database?

Ask Question
Ashutosh Kumar Verma

Ashutosh Kumar Verma

Follow
Hi! This is Ashutosh Kumar Verma. I am a software developer at MindStick Software Pvt Ltd since 2021. I have added some new and interesting features to the MindStick website like a story section, audio section, and merge profile feature on MindStick subdomains, etc. I love coding and I have good knowledge of SQL Database.

Can you answer this question?

Answer

1 Answers

Ashutosh Kumar Verma

Ashutosh Kumar Verma

18-Jul-2024

SQL Server Trigger

Triggers in a database are special stored procedures that are automatically executed or fired in response to certain events in a table or view. It is used to enforce business rules, ensure data integrity, and automate business based on data transformation.

 

Key Uses of Triggers

Data Validation and Enforcement
Verify that a value in a column meets certain criteria before allowing it to be insert or updat. For example, below created trigger is prevented from inserting the negative value into the Salary column,

CREATE TRIGGER ValidateSalary
ON Employees
BEFORE INSERT, UPDATE
AS
BEGIN
   IF EXISTS (SELECT * FROM inserted WHERE Salary < 0)
   BEGIN
       RAISERROR ('Salary cannot be negative.', 16, 1);
       ROLLBACK;
   END
END;

 

Maintain History

Triggers are also used to automatically maintain a history of records that are deleted from tables.

the below trigger is fires automatically when someone deleted the record from Employees table and insert the deleted records into Employees_History table

 

CREATE TRIGGER ValidateSalary ON Employees
AFTER DELETE
AS
BEGIN
  INSERT INTO Employees_History(EmpName, EmpName, Salary)
  SELECT EmpName, EmpName, Salary FROM deleted
END;

 


Types of Triggers

BEFORE Triggers- Perform before the triggering event (INSERT, UPDATE, DELETE). Useful for validating or editing data before commissioning.

AFTER Triggers- Perform after the triggering event has occurred. Useful for actions like logging or updating linked tables.

INSTEAD OF Triggers- Replace trigger events with intentional actions. Useful for custom tasks.

 

Benefits of Using Triggers

Automation- The rules are enforced and tasks are performed without the need for manual intervention.
Consistency- Ensure data integrity and business rules are applied consistently throughout the database.
Auditing- Provide a built-in tool to track changes in data to monitor compliance and correct deficiencies.

 

 

Also, Read: What are SQL Stored Procedures in the Database?

MindStick Training

Liked By